package com.forsir.android.mhfinance.helper;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.forsir.android.mhfinance.storeObjects.IStorable;

public class DbAdapter {
	private static final String TAG = "dbAdapter";
	private static final int DATABASE_VERSION = 70;
	private static final String DATABASE_NAME = "finance";

	public static final String ID = "_id";

	public static final String ACCOUNT_TABLE = "accounts";
	public static final String ACCOUNT_NAME = "account_name";

	public static final String CATEGORY_TABLE = "categories";
	public static final String CATEGORY_NAME = "category_name";
	public static final String CATEGORY_PARENT = "category_id";

	public static final String PAYMENT_TABLE = "payment";
	public static final String PAYMENT_ACCOUNT = "account_id";
	public static final String PAYMENT_TRANSFER_ACCOUNT = "transfer_account_id";
	public static final String PAYMENT_CATEGORY = "category_id";
	public static final String PAYMENT_DEPOSIT = "deposit";
	public static final String PAYMENT_WITHDRAW = "withdraw";
	public static final String PAYMENT_REMAINDER = "remainder";
	public static final String PAYMENT_ACTUALIZED = "actualized";
	public static final String PAYMENT_TYPE = "payment_type";
	public static final String PAYMENT_DATE = "date";
	public static final String PAYMENT_MONTH = "month";
	public static final String PAYMENT_TIME = "time"; // is not used
	public static final String PAYMENT_DESCRIPTION = "description";
	public static final String PAYMENT_REPEATING = "repeating";

	public static final String REPEATING_TABLE = "repeating";
	public static final String REPEATING_ACTUAL_DATE = "actual_date";
	public static final String REPEATING_END_DATE = "end_date";
	public static final String REPEATING_TYPE = "repeating_type";
	public static final String REPEATING_EVERY_N = "every_n";

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	private final Activity mActivity;

	/**
	 * Database creation sql statements
	 */

	private static final String PAYMENT_TABLE_CREATE = "create table " + PAYMENT_TABLE + " (" + ID + " integer primary key autoincrement, " + PAYMENT_ACCOUNT
	        + " integer not null references " + ACCOUNT_TABLE + "(" + ID + ") on delete restrict, " + PAYMENT_TRANSFER_ACCOUNT + " integer references " + ACCOUNT_TABLE
	        + "(" + ID + ") on delete restrict, " + PAYMENT_CATEGORY + " integer references " + CATEGORY_TABLE + "(" + ID + ") on delete restrict, " + PAYMENT_DEPOSIT
	        + " real not null," + PAYMENT_WITHDRAW + " real not null, " + PAYMENT_REMAINDER + " real, " + PAYMENT_ACTUALIZED + " bool null, " + PAYMENT_TYPE + " int, "
	        + PAYMENT_DATE + " long not null, " + PAYMENT_MONTH + " long not null, " + PAYMENT_DESCRIPTION + " text not null, " + PAYMENT_REPEATING
	        + " integer references " + REPEATING_TABLE + "(" + ID + ") on delete restrict);";

	private static final String ACCOUNT_TABLE_CREATE = "create table " + ACCOUNT_TABLE + " (" + ID + " integer primary key autoincrement, " + ACCOUNT_NAME
	        + " text not null);";

	private static final String CATEGORY_TABLE_CREATE = "create table " + CATEGORY_TABLE + " (" + ID + " integer primary key autoincrement, " + CATEGORY_PARENT
	        + " integer references " + CATEGORY_PARENT + "(" + ID + ") on delete restrict," + CATEGORY_NAME + " text not null);";

	private static final String REPEATING_TABLE_CREATE = "create table " + REPEATING_TABLE + " (" + ID + " integer primary key autoincrement, " + REPEATING_ACTUAL_DATE
	        + " long," + REPEATING_END_DATE + " long not null, " + REPEATING_TYPE + " integer, " + REPEATING_EVERY_N + " integer default 1);";

	private static final String INDEX_DATE = "create index if not exists index_" + PAYMENT_TABLE + " on " + PAYMENT_TABLE + " (" + PAYMENT_DATE + ");";

	public final static String SELECT_PAYMENT_WITH_ACCOUNT = PAYMENT_TABLE + " inner join " + ACCOUNT_TABLE + " on " + PAYMENT_TABLE + "." + PAYMENT_ACCOUNT + "="
	        + ACCOUNT_TABLE + "." + ID + ", " + CATEGORY_TABLE + " on " + PAYMENT_TABLE + "." + PAYMENT_CATEGORY + "=" + CATEGORY_TABLE + "." + ID;

	// payment inner join category on payment_category=category.id
	public final static String SELECT_PAYMENT_WITH_CATEGORY = PAYMENT_TABLE + " inner join " + CATEGORY_TABLE + " on " + PAYMENT_TABLE + "." + PAYMENT_CATEGORY + " = "
	        + CATEGORY_TABLE + "." + ID;

	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.w(TAG, "creating tables");
			db.execSQL(ACCOUNT_TABLE_CREATE);
			db.execSQL(CATEGORY_TABLE_CREATE);
			db.execSQL(REPEATING_TABLE_CREATE);
			db.execSQL(PAYMENT_TABLE_CREATE);
			db.execSQL(INDEX_DATE);
			// Test.fillData(db);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			if (oldVersion == 60) {
				Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ".");
				db.execSQL("DROP TABLE IF EXISTS " + REPEATING_TABLE);
				db.execSQL(REPEATING_TABLE_CREATE);
			} else if (oldVersion == 50) {
				Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ".");
				// TODO maybe copy payments
				db.execSQL("DROP TABLE IF EXISTS " + REPEATING_TABLE);
				db.execSQL(REPEATING_TABLE_CREATE);
				db.execSQL("DROP TABLE IF EXISTS " + PAYMENT_TABLE);
				db.execSQL(PAYMENT_TABLE_CREATE);
			} else {
				Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
				db.execSQL("DROP TABLE IF EXISTS " + PAYMENT_TABLE);
				db.execSQL("DROP TABLE IF EXISTS " + ACCOUNT_TABLE);
				db.execSQL("DROP TABLE IF EXISTS " + CATEGORY_TABLE);
				db.execSQL("DROP TABLE IF EXISTS " + REPEATING_TABLE);
				onCreate(db);
			}
		}
	}

	public int getVersion() {
		return DATABASE_VERSION;
	}

	public DbAdapter(Context ctx, Activity activity) {
		mCtx = ctx;
		mActivity = activity;
	}

	public DbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public long createEntry(IStorable entry) {
		final long id = mDb.insert(entry.getTableName(), null, entry.getContent());
		entry.setId(id);
		return id;
	}

	public long createEntry(String table, ContentValues context) {
		final long check = mDb.insert(table, null, context);
		return check;
	}

	public boolean deleteEntry(IStorable entry) {
		return mDb.delete(entry.getTableName(), ID + "=" + entry.getId(), null) > 0;
	}

	public boolean deleteEntries(IStorable entry, String where) {
		return mDb.delete(entry.getTableName(), where, null) > 0;
	}

	public Cursor fetchAllEntries(IStorable entry) {
		final Cursor cursor = mDb.query(entry.getTableName(), entry.getColumns(), null, null, null, null, null);
		if (mActivity != null) {
			mActivity.startManagingCursor(cursor);
		}
		return cursor;
	}

	public Cursor fetchAllEntries(String table, String[] columns) {
		final Cursor cursor = mDb.query(table, columns, null, null, null, null, null);
		if (mActivity != null) {
			mActivity.startManagingCursor(cursor);
		}
		return cursor;
	}

	public Cursor fetchAllEntries(IStorable entry, String sort) {
		final Cursor cursor = mDb.query(entry.getTableName(), entry.getColumns(), null, null, null, null, sort);
		if (mActivity != null) {
			mActivity.startManagingCursor(cursor);
		}
		return cursor;
	}

	public Cursor fetchAllEntries(IStorable entry, String where, String sort) {
		final Cursor cursor = mDb.query(entry.getTableName(), entry.getColumns(), where, null, null, null, sort);
		if (mActivity != null) {
			mActivity.startManagingCursor(cursor);
		}
		return cursor;
	}

	public Cursor fetchAllEntries(IStorable entry, String where, String sort, int count) {
		final Cursor cursor = mDb.query(entry.getTableName(), entry.getColumns(), where, null, null, null, sort, String.valueOf(count));
		if (mActivity != null) {
			mActivity.startManagingCursor(cursor);
		}
		return cursor;
	}

	public Cursor fetchEntry(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) throws SQLException {
		final Cursor cursor = mDb.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
		if (mActivity != null) {
			mActivity.startManagingCursor(cursor);
		}

		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	public Cursor fetchEntry(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, int count)
	        throws SQLException {
		final Cursor cursor = mDb.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, String.valueOf(count));
		if (mActivity != null) {
			mActivity.startManagingCursor(cursor);
		}

		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	public Cursor fetchEntry(IStorable entry) throws SQLException {
		final Cursor cursor = mDb.query(true, entry.getTableName(), entry.getColumns(), ID + "=" + entry.getId(), null, null, null, null, null);
		if (mActivity != null) {
			mActivity.startManagingCursor(cursor);
		}

		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	public IStorable getEntry(IStorable entry) {
		final Cursor cursor = fetchEntry(entry);
		if (cursor.moveToFirst()) {
			entry.fillFromCursor(cursor);
		}

		cursor.close();
		return entry;
	}

	public boolean updateEntry(IStorable entry) {
		return mDb.update(entry.getTableName(), entry.getContent(), ID + "=" + entry.getId(), null) > 0;
	}

	public int getCount(IStorable entry) {
		final Cursor cursor = fetchEntry(entry.getTableName(), new String[] { "count(*)" }, null, null, null, null, null);
		if (cursor == null) {
			return 0;
		}

		if (!cursor.moveToFirst()) {
			return 0;
		}

		final int returnCount = cursor.getInt(0);
		cursor.close();
		return returnCount;
	}

	public void beginTrasaction() {
		mDb.beginTransaction();
	}

	public void commitTrasaction() {
		mDb.setTransactionSuccessful();
	}

	public void endTrasaction() {
		mDb.endTransaction();
	}
}
